using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Utilities;
using DTN.Core.Common;
namespace com.dtn.deals
{
	public class tblCategoryFilterManager : ItblCategoryFilterManager
	{
		private static ItblCategoryFilterManager itblCategoryFilterManager = null;
		private tblCategoryFilterManager()
		{
		}
		public static ItblCategoryFilterManager CreateInstance()
		{
			if (itblCategoryFilterManager == null)
			{
				itblCategoryFilterManager = new tblCategoryFilterManager();
			}
			return itblCategoryFilterManager;
		}
		public int InserttblCategoryFilter(tblCategoryFilter tblcategoryfilter)
		{
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@Id", SqlDbType.Int),
																new SqlParameter("@CategoryId", SqlDbType.Int),
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
                                                                new SqlParameter("@PartnerId", SqlDbType.Int),
                                                                new SqlParameter("@PartnerCateId", SqlDbType.Int),
															};
			parameters[0].Direction = ParameterDirection.Output;
			parameters[1].Value = tblcategoryfilter.CategoryId;
			parameters[2].Value = tblcategoryfilter.FilterId;
            parameters[3].Value = tblcategoryfilter.PartnerId;
            parameters[4].Value = tblcategoryfilter.ParentCateId;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_InserttblCategoryFilter", CommandType.StoredProcedure, parameters);
				tblcategoryfilter.CategoryFilterId = Convert.ToInt32(parameters[0].Value);
			}
			catch (Exception ex)
			{
				tblcategoryfilter.CategoryFilterId = -1;
			}
			return tblcategoryfilter.CategoryFilterId;
		}
        public int InserttblCategoryFilter2(tblCategoryFilter tblcategoryfilter)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@Id", SqlDbType.Int),
																new SqlParameter("@CategoryId", SqlDbType.Int),
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
                                                                new SqlParameter("@PartnerId", SqlDbType.Int),
                                                                new SqlParameter("@PartnerCateId", SqlDbType.Int),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tblcategoryfilter.CategoryId;
            parameters[2].Value = tblcategoryfilter.FilterId;
            parameters[3].Value = tblcategoryfilter.PartnerId;
            parameters[4].Value = tblcategoryfilter.ParentCateId;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_InserttblCategoryFilter2", CommandType.StoredProcedure, parameters);
                tblcategoryfilter.CategoryFilterId = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tblcategoryfilter.CategoryFilterId = -1;
            }
            return tblcategoryfilter.CategoryFilterId;
        }


		public bool UpdatetblCategoryFilter(tblCategoryFilter tblcategoryfilter)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@Id", SqlDbType.Int),
																new SqlParameter("@CategoryId", SqlDbType.Int),
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
                                                                  new SqlParameter("@PartnerId", SqlDbType.Int),
                                                                  new SqlParameter("@PartnerCateId", SqlDbType.Int),
															};
			parameters[0].Value = tblcategoryfilter.CategoryFilterId;
			parameters[1].Value = tblcategoryfilter.CategoryId;
			parameters[2].Value = tblcategoryfilter.FilterId;
            parameters[3].Value = tblcategoryfilter.PartnerId;
            parameters[4].Value = tblcategoryfilter.ParentCateId;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_UpdatetblCategoryFilter", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}

        public bool UpdatetblCategoryFilterByPartner(int partnerId) 
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
                                                                  new SqlParameter("@PartnerId", SqlDbType.Int),
															};
            parameters[0].Value = partnerId;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_UpdatetblCategoryFilterByPartner", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

		public bool DeletetblCategoryFilter(int id)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@CategoryFilterId", SqlDbType.Int),
															};
			parameters[0].Value = id;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_DeletetblCategoryFilter", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}
		public tblCategoryFilter SelecttblCategoryFilterByID(int id)
		{
			IDataReader dr;
			tblCategoryFilter tblcategoryfilter = new tblCategoryFilter();
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@CategoryFilterId", SqlDbType.Int),
															};
			parameters[0].Value = id;
			try
			{
				dr = dataProvider.ExecuteReader("sp_SelecttblCategoryFilter", CommandType.StoredProcedure, parameters);
				while(dr.Read())
				{
					tblcategoryfilter.CategoryFilterId = Convert.ToInt32(dr["Id"].ToString());
					tblcategoryfilter.CategoryId = Convert.ToInt32(dr["CategoryId"].ToString());
                    tblcategoryfilter.FilterId = Convert.ToInt32(dr["GroupFilterId"].ToString());
                    tblcategoryfilter.PartnerId = Convert.ToInt32(dr["PartnerId"].ToString());
				}

			}
			catch (Exception ex)
			{
				return null;
			}
			return tblcategoryfilter;
		}
		public DataTable SelecttblCategoryFiltersDynamic(int StartIndex,int MaxRecords,string Where, string Order)
		{
			DataSet ds = new DataSet();
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			SqlParameter[] parameters = new SqlParameter[]{
                                                                new SqlParameter("@Startindex", SqlDbType.Int),
																new SqlParameter("@Maxrecords", SqlDbType.Int),
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
			parameters[0].Value = StartIndex;
			parameters[1].Value = MaxRecords;
            parameters[2].Value = Where;
            parameters[3].Value = Order;
			try
			{
				ds = dataProvider.ExecuteDataSet("sp_SelecttblCategoryFiltersDynamic", CommandType.StoredProcedure, parameters);
			}
			catch (Exception ex)
			{
				return null;
			}
			return ds.Tables["Table"];
		}

        //Cuong Code
        public static DataTable GetAndPagingByFilter(int parentcategoryId,string filter, int _pageSize,
                                                  int _currentPage, out int _totalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tblCategoryFilter_GetAndPagingByFilter", dbConn);
            dbCmd.Parameters.AddWithValue("@PageSize", _pageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", _currentPage);
            dbCmd.Parameters.AddWithValue("@Filter", filter);
            dbCmd.Parameters.AddWithValue("@ParentCategory", parentcategoryId);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

                _totalRecord = ConvertUtility.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAndPagingByFilterV2(int parentcategoryId, int categoryid, string filter, Enumerations.GroupOrder OrderBy, int _pageSize,
                                                  int _currentPage, out int _totalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tblCategoryFilter_GetAndPagingByFilterV2", dbConn);
            dbCmd.Parameters.AddWithValue("@PageSize", _pageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", _currentPage);
            dbCmd.Parameters.AddWithValue("@Order", (int)OrderBy);
            dbCmd.Parameters.AddWithValue("@ListFilter", filter);
            dbCmd.Parameters.AddWithValue("@ParentCategory", parentcategoryId);
            dbCmd.Parameters.AddWithValue("@CategoryId", categoryid);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

                _totalRecord = ConvertUtility.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }



        public bool IsExistedFilterInTblCateFilter(int id)
        {
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
            try
            {

                dbConn.Open();
                SqlCommand dbCmd = new SqlCommand("sp_IsExistedGroupFilterIDInTblCateFilter", dbConn);
                dbCmd.Parameters.AddWithValue("@ID", id);
                dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                dbCmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
                dbCmd.CommandType = CommandType.StoredProcedure;

                int kq = dbCmd.ExecuteNonQuery();
                int totalrow = int.Parse(dbCmd.Parameters["@RETURN_VALUE"].Value.ToString());
                if (totalrow > 0)
                    return true;

            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                dbConn.Close();
            }
            return false;
        }


        public bool UpdateGroupFilterIdByGroupFilterID(int oldId, int newId)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
                                                                  new SqlParameter("@OldID", SqlDbType.Int){Value = oldId},
                                                                  new SqlParameter("@NewID",SqlDbType.Int){ Value = newId}
															};
            
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Update_tblCategoryFilter_WithGroupFilterID", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }



        public DataTable SelectCateFilterByPartnerID(int partnerID)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
                                                                new SqlParameter("@ID", SqlDbType.Int){Value = partnerID}
																
															};
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelectCateFilterByPartnerID", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }


        public DataTable SelectAllGroupFilterByPartnerID(int id)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
                                                                new SqlParameter("@ID", SqlDbType.Int){Value = id}
																
															};
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelectAllGroupFilterByPartnerID", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }


        public bool DeleteFilterByPartnerId(int pID)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = pID;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_DeleteCategoryFilterByPartnerID", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
    }
}
